IF OBJECT_ID('dbo.RptSubContractMetricSnapshot') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptSubContractMetricSnapshot
    IF OBJECT_ID('dbo.RptSubContractMetricSnapshot') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptSubContractMetricSnapshot >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptSubContractMetricSnapshot >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptSubContractMetricSnapshot
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/14/2008
--
-- Description: Takes a snapshot of data and puts into table for Reporting
--        later. Supposed to run on Sunday night, for weekending Sunday.
--
--
-- Parameters:  
--        @AsOfDate           Used to get Week Start and End dates
--        @ForceAsofDate      Forces proc to use above date, instead of
--                            figuring a better date for non Sat/Sun runs.
--        @DeletePrior        Default behavior deactivates prior records 
--                            (in rerun) this flag=1 will force delete.
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptSubContractMetricSnapshot(
--                       @AsOfDate                = '6/1/2008')
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/14/08    KC        Created
-- 10/23/08    KC        Many changes made, in the past week. Looks to be
--                       all set by this date.
-- 12/5/08     KC        Changed default behavior, but kept Params.
--                       Added 3 new statuses to match other reports.
--
-- =======================================================================
CREATE PROCEDURE dbo.RptSubContractMetricSnapshot
     (
     @AsOfDate                          DATETIME = NULL,
     @Institution                       VARCHAR(100) = NULL,
     @ForceAsOfDate                     BIT = 0,
     @DeletePrior                       BIT = 0
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     SET ANSI_WARNINGS OFF
     
     -- Declare Local Variables
     DECLARE   @Error              INT,
               @RowCount           INT,
               @ReturnCode         INT,
               @NewAsOfDate        DATETIME,
               @StartDate          DATETIME,
               @EndDate            DATETIME
     
     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     
     -- If not date, get current
     IF @AsOfDate IS NULL SET @AsOfDate = GETDATE()
     
     -- If running on Sun, just use given date, otherwise, get prior
     -- Sunday. Unless ForceAsOfDate is 1
     SET @NewAsOfDate = CASE WHEN DATEPART(dw,@AsofDate) IN (1) OR @ForceAsOfDate = 1
                              THEN dbo.fnRemoveTime(@AsOfDate)
                              ELSE dbo.fnRemoveTime(DATEADD(dd,1-DATEPART(dw,@AsOfDate),@AsOfDate))
                         END

     -- Now get Week Start and End Dates
     SELECT    @StartDate = StartDate,
               @EndDate = EndDate
     FROM      dbo.fnGetStartEndofWeek( @NewAsOfDate,'MON')

     -- Testing
     --SELECT    @AsOfDate AS AsOfDate,
     --          @NewAsOfDate AS NewAsOfDate,
     --          @StartDate AS WeeklyStartDate,
     --          @EndDate AS WeeklyEndDate
                    
     -- If Any records exist for given Start and End Week, then we are done..
     IF EXISTS (SELECT   *
               FROM      dbo.SubContractDeliverableSnapShot
               WHERE     WeekEndDate = @EndDate)
     BEGIN
          IF @DeletePrior = 1
               DELETE 
               FROM      dbo.SubContractDeliverableSnapShot
               WHERE     WeekEndDate = @EndDate
          ELSE
               BEGIN
                    PRINT 'DeletePrior set to ' + CONVERT(CHAR(1),@DeletePrior) + ' Records for WeekEnding ' + 
                                   CONVERT(VARCHAR(20),@EndDate,101) + '.already exist. Exiting Normally.'
                    RETURN 0
               END

          -- check for errors
          SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
          IF @Error != 0 
          BEGIN
               PRINT 'Error on Delete of Prior Records. Exiting.'
               RETURN -1
          END
               
          PRINT 'DeletePrior set to ' + CONVERT(CHAR(1),@DeletePrior) + '. Deleted ' +
                    CONVERT(VARCHAR(20),@RowCount) + ' Prior Records for WeekEnding ' + 
                    CONVERT(VARCHAR(20),@EndDate,101) + '.'
     
     END

     -- Insert STATUS Records
     INSERT    dbo.SubContractDeliverableSnapShot
          (AsOfDate,                    
          WeekStartDate,      
          WeekEndDate,             
          AgreementId,          
          FolderNumber,       
          InfoEdPropNumber,         
          ProposalShortTitle,       
          Institution,       
          InstitutionId,       
          Department,       
          DepartmentId,       
          Unit,       
          UnitId,       
          ChiefCode,       
          InstrumentType,          
          PrincipalInvestigatorId, 
          PrincipalInvestigator,   
          SponsorID,                
          SponsorName,              
          SubContractorId,         
          SubContractorName,       
          Specialist,              
          ReportId,                
          ReportName,              
          REPORTCAT,                    
          REPORTSTAT,              
          SubmittedDate,           
          DueDate,                 
          ProcessedDate,           
          CompletedDate,           
          SubmittedBy,             
          StatusBy,                
          CompletedBy,             
          PeriodStartDate,         
          PeriodEndDate,           
          BudgetStartDate,         
          BudgetEndDate,           
          FundNumber,              
          REPORTCAT_CODE,               
          REPORTSTAT_CODE)
     SELECT    
          @AsOfDate,                    -- AsOfDate,                  
          @StartDate,                   -- WeekStartDate,        
          @EndDate,                     -- WeekEndDate,               
          AI.AgreementId,               -- AgreementId,          
          AI.FolderNumber,              -- FolderNumber,         
          AI.InfoEdPropNumber,          -- InfoEdPropNumber,         
          AI.ProposalShortTitle,        -- ProposalShortTitle,       
          ISNULL(AI.Institution,O.Name),-- Institution,       
          AI.InstitutionId,             -- InstitutionId,       
          AI.Department,                -- Department,       
          AI.DepartmentId,              -- DepartmentId,       
          AI.Unit,                      -- Unit,       
          AI.UnitId,                    -- UnitId,       
          AI.ChiefCode,                 -- ChiefCode,       
          AI.InstrumentType,            -- InstrumentType,       
          AI.PrincipalInvestigatorId,   -- PrincipalInvestigatorId,   
          AI.PrincipalInvestigator,     -- PrincipalInvestigator,     
          AI.SponsorId,                 -- SponsorID,                
          AI.SponsorName,               -- SponsorName,              
          AI.SubContractorId,           -- SubContractorId,      
          COALESCE(AI.SecondarySponsor,SUB.SubOutContractorName),     -- SubContractorName,         
          DEL.SubmittedBy,              -- Specialist,           
          DEL.ReportId,                 -- ReportId,                  
          DEL.ReportName,               -- ReportName,           
          DEL.REPORTCAT,                -- REPORTCAT,                 
          CASE WHEN DEL.REPORTSTAT_CODE IN ('REPORTSTAT_40','REPORTSTAT_41') THEN 'UnExecutedAgreementRecd'
               WHEN DEL.REPORTSTAT_CODE IN ('REPORTSTAT_19','REPORTSTAT_28') THEN 'PendingGA'
               WHEN DEL.REPORTSTAT_CODE IN ('REPORTSTAT_24','REPORTSTAT_33') THEN 'PendingDocFromDeptPI'
               WHEN DEL.REPORTSTAT_CODE IN ('REPORTSTAT_20','REPORTSTAT_29') THEN 'PendingDeptPIAppr'
               WHEN DEL.REPORTSTAT_CODE IN ('REPORTSTAT_16','REPORTSTAT_25') THEN 'InQueue' 
               WHEN DEL.REPORTSTAT_CODE IN ('REPORTSTAT_17','REPORTSTAT_26') THEN 'Assigned'  
               WHEN DEL.REPORTSTAT_CODE IN ('REPORTSTAT_18','REPORTSTAT_27') THEN 'InNegotiation'
               WHEN DEL.REPORTSTAT_CODE IN ('REPORTSTAT_21','REPORTSTAT_30') THEN 'PartiallyExecuted'
               WHEN DEL.REPORTSTAT_CODE IN ('REPORTSTAT_22','REPORTSTAT_31') THEN 'FullyExecuted'
               WHEN DEL.REPORTSTAT_CODE IN ('REPORTSTAT_23','REPORTSTAT_32') THEN 'OtherUnresolved'
               WHEN DEL.REPORTSTAT_CODE IN ('REPORTSTAT_34','REPORTSTAT_35') THEN 'PendingOutYears'
          END,                          -- REPORTSTAT,           
          DEL.SubmittedDate,            -- SubmittedDate,             
          DEL.DueDate,                  -- DueDate,                   
          DEL.StatusDate,               -- ProcessedDate,             
          DEL.CompletedDate,            -- CompletedDate,             
          DEL.SubmittedBy,              -- SubmittedBy,               
          DEL.StatusBy,                 -- StatusBy,                  
          DEL.CompletedBy,              -- CompletedBy,               
          BUDG.ProjectStartDate,        -- PeriodStartDate,      
          BUDG.ProjectEndDate,          -- PeriodEndDate,             
          BUDG.BudgetStartDate,         -- BudgetStartDate,      
          BUDG.BudgetEndDate,           -- BudgetEndDate,             
          BUDG.FundNumber,              -- FundNumber,           
          DEL.REPORTCAT_CODE,           -- REPORTCAT_CODE,            
          DEL.REPORTSTAT_CODE           -- REPORTSTAT_CODE
     FROM      dbo.AgrAgreement_Info AI 
               LEFT OUTER JOIN
                    dbo.FWKDomainOrganization O ON AI.InstitutionId = O.Id and O.IsActive = 1
               INNER JOIN
                    (SELECT D.prop_no,
                            D.ReportId,
                            D.ReportName,
                            D.REPORTCAT_CODE,
                            D.REPORTCAT,
                            D.REPORTSTAT,
                            D.REPORTSTAT_CODE,
                            D.DueDate,
                            D.SubmittedDate,
                            D.ProcessedDate AS StatusDate,
                            D.CompletedDate,
                            D.SubmittedBy,
                            D.StatusBy,
                            D.CompletedBy,
                            D.PeriodStartDate,
                            D.PeriodEndDate,
                            D.Comments
                    FROM    dbo.vwInfoEdSubContractDeliverables D
                            INNER JOIN (SELECT  ReportId,
                                                MAX(ProcessedDate) AS LastDate
                                        FROM    dbo.vwInfoEdSubContractDeliverables
                                        WHERE   ProcessedDate <= @EndDate AND   
                                                                           -- SubIn            SubOut
                                                  REPORTSTAT_CODE IN ('REPORTSTAT_16','REPORTSTAT_25',   -- In Queue
                                                                      'REPORTSTAT_17','REPORTSTAT_26',   -- Assigned
                                                                      'REPORTSTAT_18','REPORTSTAT_27',   -- In Negotiation
                                                                      'REPORTSTAT_19','REPORTSTAT_28',   -- Pending GA
                                                                      'REPORTSTAT_20','REPORTSTAT_29',   -- Pending Dept./PI Approval
                                                                      'REPORTSTAT_21','REPORTSTAT_30',   -- Partially Executed
                                                                      'REPORTSTAT_22','REPORTSTAT_31',   -- Fully Executed
                                                                      'REPORTSTAT_23','REPORTSTAT_32',   -- Other/Unresolved
                                                                      'REPORTSTAT_40','REPORTSTAT_41',   -- UnExecutedAgreementRecd
                                                                      'REPORTSTAT_24','REPORTSTAT_33',   -- PendingDocFromDeptPI
                                                                      'REPORTSTAT_34','REPORTSTAT_35')   -- PendingOutYears
                                                                      
                                                                      
                                                                      
                                        GROUP BY ReportId) LAST ON D.ReportId = LAST.ReportId AND 
                              D.ProcessedDate = LAST.LastDate ) DEL ON AI.InfoEdPropNumber = DEL.prop_no
               
               LEFT OUTER JOIN
                    -- This function uses the Agreement_Fct table to get Budget and Project data
                    dbo.fnGetFactTableBudget(@StartDate, @EndDate) BUDG ON AI.AgreementId = BUDG.AgreementId
                    
               LEFT OUTER JOIN
                    (SELECT DISTINCT SCM.AgrAgreementId,
                              SC.Id AS SubOutContractorId,
                              SC.Name AS SubOutContractorName
                    FROM      dbo.AgrAgreement_SubContractor_Map SCM  
                              INNER JOIN dbo.AgrSubContractor SC ON SCM.AgrSubContractorId = SC.Id) SUB 
                                        ON AI.AgreementId = SUB.AgrAgreementId
     WHERE     -- Need this? Already checking in SubSelect on Deliverables Above.
               DEL.StatusDate <= @EndDate AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution)))                                
          
     -- check for errors
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error != 0 
     BEGIN
          PRINT 'Error on Insert to dbo.SubContractDeliverableSnapShot (STATUS). Exiting.'
          RETURN -1
     END
               
     PRINT 'Inserted ' + CONVERT(VARCHAR(20),@RowCount) + ' STATUS Records for WeekEnding ' + 
               CONVERT(VARCHAR(20),@EndDate,101) + ' to table, '
     
     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptSubContractMetricSnapshot') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptSubContractMetricSnapshot >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptSubContractMetricSnapshot >>>'
go

GRANT EXECUTE ON dbo.RptSubContractMetricSnapshot TO InsightReport,Insight
go


